﻿using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Drawing;

public partial class _Default : System.Web.UI.Page 
{
    SqlConnection con = new SqlConnection();
    SqlDataAdapter da;
    DataSet ds;
    SqlCommandBuilder cb;
    DataTable dt;
    DataView dv;
    protected void Page_Load(object sender, EventArgs e)
    {
        string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        con.ConnectionString = constr;
        con.Open();
        if (this.IsPostBack == false)
        {
            LoadAll();


        }
   
        btnUpdate.Enabled = false;
        btnDelete.Enabled = false;

        
        
    }
  

    void LoadAll()
    {
       
        LoadProductInfo();
        LoadDDLCategory();
        LoadDDLSupplier();
        LoadDDLFilterCategory();
        LoadDDLFilterSupplier();
        

    }

    private void clearForm()
    {
        txtID.Text = "";
        txtProductName.Text = "";
        txtPrice.Text = "";
        chkDiscontinued.Checked = false;
        txtSearch.Text = "";
        ddlCategory.SelectedItem.Text = null;
        ddlSupplier.SelectedItem.Text = null;
        gvProducts.BackColor = Color.White;
        btnAdd.Enabled = true;
        btnUpdate.Enabled = false;
        btnDelete.Enabled = false;
        Session["Product_index"] = -1;
        Session["Product_btnSearchActive"] = false;
        Session["Product_FilterCategory"] = false;
        Session["Product_FilterSupplier"] = false;


    }

 

    void LoadProductInfo()
    {
        da = new SqlDataAdapter("SELECT * FROM Production.Products", con);

        dt = new DataTable("Production.Products");
       
        da.Fill(dt);

        gvProducts.AutoGenerateSelectButton = true;
        gvProducts.AllowPaging = true;
        gvProducts.PageSize = 12;

        gvProducts.DataSource = dt;
        gvProducts.DataBind();

    }

    public void LoadDDLFilterCategory()
    {

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;

        SqlDataAdapter da = new SqlDataAdapter("SELECT categoryid, categoryname FROM Production.Categories", con);
        DataTable dt = new DataTable("Production.Categories");
        da.Fill(dt);
        ddlFilterCategory.DataSource = dt;
        ddlFilterCategory.DataTextField = "categoryname";
        ddlFilterCategory.DataValueField = "categoryid";
        ddlFilterCategory.DataBind();


    }

    public void LoadDDLFilterSupplier()
    {
        
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;

        SqlDataAdapter da = new SqlDataAdapter("SELECT supplierid, companyname FROM Production.Suppliers", con);
        DataTable dt = new DataTable("Production.Suppliers");
        da.Fill(dt);
        ddlFilterSupplier.DataSource = dt;
        ddlFilterSupplier.DataTextField = "companyname";
        ddlFilterSupplier.DataValueField = "supplierid";
        ddlFilterSupplier.DataBind();

    }

    public void LoadDDLCategory()
    {
        
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;

        SqlDataAdapter da = new SqlDataAdapter("SELECT categoryid, categoryname FROM Production.Categories", con);
        DataTable dt = new DataTable("Production.Categories");
        da.Fill(dt);
        ddlCategory.DataSource = dt;
        ddlCategory.DataTextField = "categoryname";
        ddlCategory.DataValueField = "categoryid";
        ddlCategory.DataBind();



    }

    public void LoadDDLSupplier()
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = con;

        SqlDataAdapter da = new SqlDataAdapter("SELECT supplierid, companyname FROM Production.Suppliers", con);
        DataTable dt = new DataTable("Production.Suppliers");
        da.Fill(dt);
        ddlSupplier.DataSource = dt;
        ddlSupplier.DataTextField = "companyname";
        ddlSupplier.DataValueField = "supplierid";
        ddlSupplier.DataBind();
    }
    protected void gvProducts_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        
        LoadProductInfo();
       
        if ((bool)Session["Product_btnSearchActive"] == true)
        {
            gvProducts.PageIndex = e.NewPageIndex;
            dv = new DataView(dt);
            dv.RowFilter = string.Format("productname like '%{0}%' ", txtSearch.Text);
            gvProducts.DataSource = dv;
            gvProducts.DataBind();
            Session["Product_index"] = -1;

        }
        else if((bool)Session["Product_FilterCategory"]==true)
        {
            gvProducts.PageIndex = e.NewPageIndex;
            dv = new DataView(dt);
            dv.RowFilter = string.Format("categoryid = {0} ", ddlFilterCategory.SelectedValue);
            gvProducts.DataSource = dv;
            gvProducts.DataBind();
            Session["Product_index"] = -1;
        }
        else if ((bool)Session["Product_FilterSupplier"] == true)
        {
            gvProducts.PageIndex = e.NewPageIndex;
            dv = new DataView(dt);
            dv.RowFilter = string.Format(" supplierid = {0} ", ddlFilterSupplier.SelectedValue);
            gvProducts.DataSource = dv;
            gvProducts.DataBind();
            Session["Product_index"] = -1;
        }
        else{
 gvProducts.PageIndex = e.NewPageIndex;
            gvProducts.DataBind();
            Session["Product_index"] = -1;
        }
    }
    protected void gvProducts_SelectedIndexChanged(object sender, EventArgs e)
    {
        btnUpdate.Enabled = true;
        btnDelete.Enabled = true;
        btnAdd.Enabled = false;

        GridViewRow r = gvProducts.SelectedRow;

        int index = (int)Session["Product_index"];

        if (gvProducts.SelectedRow.RowIndex != index && index != -1)
        {
            gvProducts.Rows[index].BackColor = Color.White;

        }
        index = gvProducts.SelectedRow.RowIndex;
        Session["Product_index"] = index;
        r.BackColor = Color.Yellow;
        txtID.Text = r.Cells[1].Text;
        txtProductName.Text = r.Cells[2].Text;
        ddlSupplier.SelectedValue = r.Cells[3].Text;
        ddlCategory.SelectedValue = r.Cells[4].Text;
        txtPrice.Text = r.Cells[5].Text;
        CheckBox chk = (CheckBox)r.Cells[6].Controls[0];
        chkDiscontinued.Checked = chk.Checked;

            
    }
    protected void btnDelete_Click(object sender, EventArgs e)
    {
        try
        {
            
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "DeleteProduct";

            SqlParameter param = new SqlParameter("@ProID", SqlDbType.Int);
            param.Value = int.Parse(txtID.Text.ToString());
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            Response.Write("<script language='JavaScript'> alert('DELETE SUCCESSFULLY'); </script>");
            Session["Product_index"] = -1;
            Session["Product_btnSearchActive"] = false;
            Session["Product_FilterCategory"] = false;
            Session["Product_FilterSupplier"] = false;


        }
        catch (Exception ex)
        {
            //Response.Write("<script>alert('" + ex.Message + "')</script>");
            Response.Write("<script language='JavaScript'> alert('SORRY! YOU CANNOT DELETE THIS ROW'); </script>");

        }
        finally
        {
            
        }

       
        LoadProductInfo();
    }

    void AddNewProduct()
    {



        try
        {
            
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "AddProduct";

            SqlParameter param = new SqlParameter("@Proname", SqlDbType.NVarChar);
            param.Size = 40;
            param.Value = Server.HtmlDecode(txtProductName.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@SupID", SqlDbType.Int);

            param.Value = ddlSupplier.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@CatID", SqlDbType.Int);

            param.Value = ddlCategory.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@Price", SqlDbType.Money);

            param.Value = Double.Parse(txtPrice.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@Discontinued", SqlDbType.Bit);
            if (chkDiscontinued.Checked)
                param.Value = 1;
            else param.Value = 0;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            Response.Write("<script language='JavaScript'> alert('ADD SUCCESSFULLY!'); </script>");
        }
        catch (Exception ex)
        {
            //Response.Write("<script language='JavaScript'> alert('ADD FAILED'); </script>");

        }
        finally
        {
            
        }
        








    }
    protected void btnAdd_Click(object sender, EventArgs e)
    {
        AddNewProduct();
        Session["Product_index"] = -1;
        Session["Product_btnSearchActive"] = false;
        Session["Product_FilterCategory"] = false;
        Session["Product_FilterSupplier"] = false;
        LoadProductInfo();
    }

    void UpdateProduct()
    {



        try
        {
            
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "UpdateProduct";

            SqlParameter param = new SqlParameter("@ProID", SqlDbType.Int);
            param.Value = int.Parse(txtID.Text.ToString());
            cmd.Parameters.Add(param);

            param = new SqlParameter("@Proname", SqlDbType.NVarChar);
            param.Size = 40;
            param.Value = txtProductName.Text.ToString();
            cmd.Parameters.Add(param);

            param = new SqlParameter("@SupID", SqlDbType.Int);

            param.Value = ddlSupplier.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@CatID", SqlDbType.Int);

            param.Value = ddlCategory.SelectedValue;
            cmd.Parameters.Add(param);

            param = new SqlParameter("@Price", SqlDbType.Money);

            param.Value = Double.Parse(txtPrice.Text);
            cmd.Parameters.Add(param);

            param = new SqlParameter("@Discontinued", SqlDbType.Bit);
            if (chkDiscontinued.Checked)
                param.Value = 1;
            else param.Value = 0;
            cmd.Parameters.Add(param);

            cmd.ExecuteNonQuery();
            Response.Write("<script language='JavaScript'> alert('UPDATE SUCCESSFULLY'); </script>");
        }
        catch (Exception ex)
        {
            Response.Write("<script language='JavaScript'> alert('UPDATE FAILED'); </script>");

        }
        finally
        {
            
        }





    }
    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        UpdateProduct();
        Session["Product_index"] = -1;
        Session["Product_btnSearchActive"] = false;
        Session["Product_FilterCategory"] = false;
        Session["Product_FilterSupplier"] = false;
        LoadProductInfo();
        btnUpdate.Enabled = false;
        btnDelete.Enabled = false;
    }
    protected void btnNew_Click(object sender, EventArgs e)
    {
        clearForm();
        LoadAll();
    }


    protected void txtSearch_TextChanged(object sender, EventArgs e)
    {
        

    }
    protected void btnSearch_Click(object sender, EventArgs e)
    {
       

            
            LoadProductInfo();
            Session["Product_btnSearchActive"] = true;
            
            Session["Product_FilterCategory"] = false;
            Session["Product_FilterSupplier"] = false;
            dv = new DataView(dt);
            dv.RowFilter = string.Format("productname like '%{0}%' ", txtSearch.Text);
            gvProducts.DataSource = dv;
            gvProducts.DataBind();
        
        Session["Product_index"] = -1;
    }



  
    protected void btnFilter_Click(object sender, EventArgs e)
    {
       
            LoadProductInfo();
            Session["Product_FilterCategory"] = true;
            Session["Product_btnSearchActive"] = false;
         
            Session["Product_FilterSupplier"] = false;
            dv = new DataView(dt);
            dv.RowFilter = string.Format("categoryid = {0} ", ddlFilterCategory.SelectedValue);
            gvProducts.DataSource = dv;
            gvProducts.DataBind();
       

        Session["Product_index"] = -1;
    }

    protected void btnFilterSupplier_Click(object sender, EventArgs e)
    {
        LoadProductInfo();
        Session["Product_FilterSupplier"] = true;
        Session["Product_btnSearchActive"] = false;
        Session["Product_FilterCategory"] = false;
        dv = new DataView(dt);
        dv.RowFilter = string.Format("supplierid = {0} ", ddlFilterSupplier.SelectedValue);
        gvProducts.DataSource = dv;
        gvProducts.DataBind();


        Session["Product_index"] = -1;
        
        
    }
}
